package com.laundry.tables;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.Date;

import com.laundry.lib.Database;
import com.laundry.lib.Machine;
import com.laundry.lib.MachineDateAvailability;


public class LaundryMachine {
/*Machine machine;
Date date;
boolean isAvailable;
String remarks;
*/
	
	/* Machine Master List operation  (Table : laundry_machinemasterlist)*/
	public synchronized Machine addMachine(Machine machine){
		String addMachine="insert into laundromat.laundry_machinemasterlist values(?,?,?)";
		String getMachineId="SELECT ifNull( max(machineId)+1,1) as machineId FROM laundromat.laundry_machinemasterlist ";
		Connection connection;
		Statement statement;
		PreparedStatement preparedStatement;
		int newMachineId=-1;
		
		try {
			/* Get new machine Id */
			connection = Database.getConnection();
			statement = connection.createStatement();			
			ResultSet resultSet = statement.executeQuery(getMachineId);
			while (resultSet.next()) {
				newMachineId = resultSet.getInt(1);
				break;
            }			
			resultSet.close();
			statement.close();
			// add new Machine			
			preparedStatement= connection.prepareStatement(addMachine);
			preparedStatement.setInt(1, newMachineId);
			preparedStatement.setBoolean(2, machine.isLive());
			preparedStatement.setString(3, machine.getRemarks());
			
			System.out.print(" added "+  preparedStatement.executeUpdate()+" machine ");
			machine.setMachineId(newMachineId);
			
			preparedStatement.close();
			
			connection.close();
			
			
		} catch (Exception e) {
			// TODO Auto-generated catch block
			
			e.printStackTrace();
		}
        
		return machine;
	}
	public synchronized void editMachine(Machine machine ){
		String edit="update laundromat.laundry_machinemasterlist set isLive=? , remarks=? where machineid=?";
		Connection connection;		
		PreparedStatement preparedStatement;
		
		try {
			connection=Database.getConnection();
			preparedStatement=connection.prepareStatement(edit);
			preparedStatement.setBoolean(1, machine.isLive());
			preparedStatement.setString(2, machine.getRemarks());
			preparedStatement.setInt(3, machine.getMachineId());
			
			preparedStatement.executeUpdate();
			
			preparedStatement.close();
			connection.close();
		} catch (Exception e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
	}
	public synchronized ArrayList<Machine> getMachines(boolean isLive){
		
		ArrayList<Machine> machines=new ArrayList<Machine>();
		String getMachine="SELECT machineId,isLive,remarks FROM laundromat.laundry_machinemasterlist where isLive=?";
		Connection connection;
		Statement statement;
		PreparedStatement preparedStatement;
		try {
			connection=Database.getConnection();
			//statement=connection.createStatement();
			preparedStatement= connection.prepareStatement(getMachine);
			preparedStatement.setBoolean(1, isLive);
			ResultSet results= preparedStatement.executeQuery();
			 
			while(results.next()){
				 machines.add(new Machine(results.getInt(1),results.getBoolean(2),results.getString(3)));
			 }
			results.close();
			connection.close();
		} catch (Exception e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		return machines;
	}
	
	
	public synchronized ArrayList<MachineDateAvailability> getMachinesOnADate(Date date,boolean isLive){
		// dummy code. need to update 
			String query="SELECT lm.machineId,l.Date,l.isAvailabe,l.Remarks FROM laundromat.laundry_machinemasterlist lm left outer join " +
					"		laundromat.laundry_machines l on lm.machineId=l.machineId and date=? where lm.isLive=?";
			Connection connection;
			PreparedStatement preparedStatement;
			ArrayList<MachineDateAvailability> machines= new ArrayList<MachineDateAvailability>();
			try {
				connection=Database.getConnection();
				preparedStatement=connection.prepareStatement(query);
				preparedStatement.setDate(1, new java.sql.Date(Date.parse(date.toGMTString())));
				int live=isLive?1:0;
				preparedStatement.setInt(2, live);
				ResultSet result=preparedStatement.executeQuery();
				while(result.next()){
					machines.add(new MachineDateAvailability(result.getInt(1),result.getDate(2),result.getBoolean(3),result.getString(4)));
				}
				
			} catch (Exception e) {
				// TODO Auto-generated catch block
				e.printStackTrace();
			}
		
		return machines;
		
	}
	/* Machine Daily Availabilty Opertaion (Table :laundry_machines)*/
	public int addMachineAvailability(MachineDateAvailability machine){
		int rows=-1;
		Connection connection;
		Statement statement;
		String checkForExistance="select count(*) from laundry_machines  where machineid=? and date=?";
		
		try {
			connection=Database.getConnection();
			PreparedStatement preparedStatement=connection.prepareStatement(checkForExistance);
			preparedStatement.setInt(1, machine.getMachineId());
			preparedStatement.setDate(2, new java.sql.Date(Date.parse(machine.getDate().toGMTString())));
			ResultSet resultSet= preparedStatement.executeQuery();
			resultSet.next();
			int alreadyExist=resultSet.getInt(1);
			resultSet.close();
			connection.close();		
			
			if(alreadyExist==0){ // insert				
				String insert = "insert into laundry_machines values ";			
				int isAvailable=machine.isAvailable()?1:0;
				insert+=" ( "+machine.getMachineId()+",'"+new java.sql.Date(Date.parse(machine.getDate().toGMTString()))+
							"' , "+isAvailable+",'"+machine.getRemarks()+"')";		
				//insert= insert.substring(0,insert.length()-1);
				insert+=" ";		
				connection=Database.getConnection();
				statement=connection.createStatement();
				rows= statement.executeUpdate(insert);
				statement.close();
				connection.close();
			}
			// update
			else{
				String update = " update laundry_machines set isAvailabe=? , remarks=? where machineId=? and date=?";
				connection=Database.getConnection();
				preparedStatement=connection.prepareStatement(update);
				
				preparedStatement.setInt(1, machine.isAvailable()?1:0);
				preparedStatement.setString(2, machine.getRemarks());
				preparedStatement.setInt(3, machine.getMachineId());
				preparedStatement.setDate(4, new java.sql.Date(Date.parse(machine.getDate().toGMTString())));
				
				rows= preparedStatement.executeUpdate();
				
				connection.close();
			}
		} catch (Exception e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}						
		return  rows;
	}
	public void editMachineAvailability(int machineId,Date date,boolean isAvailable,String remarks){

	}
	public int getMachineAvailability(Date date){
		java.sql.Date sqlDate = new java.sql.Date(Date.parse(date.toGMTString()));
		int numberOfMachines=-1;
		String getMacQuery="SELECT count(*) FROM laundromat.laundry_machines where date=? and IsAvailabe=1";
		try{
			Connection connection=Database.getConnection();
			PreparedStatement preparedStatement= connection.prepareStatement(getMacQuery);
			preparedStatement.setDate(1, sqlDate);
			ResultSet resultSet= preparedStatement.executeQuery();
			resultSet.next();
			numberOfMachines=  resultSet.getInt(1);
			resultSet.close();
			connection.close();
		}
		catch(Exception e){
			e.printStackTrace();
		}
		return numberOfMachines;
	}
}
